package com.fowo.api.common.mybatis.helper;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.fowo.api.common.meta.MetaDataField;
import com.fowo.api.common.meta.MetaDataObject;
import com.fowo.api.common.meta.MetaDataObjects;
import com.fowo.api.common.meta.ReferenceCustomField;
import com.fowo.api.common.model.PageSearch;
import com.fowo.api.common.model.PageSearchFilter;
import com.fowo.api.common.model.RException;
import com.fowo.api.common.model.PageSearch.OptionalJoinField;
import com.fowo.api.common.util.ContextHolder;
import com.fowo.api.common.util.DateUtils;
import com.fowo.api.common.util.ListUtils;
import com.fowo.api.sys.rdp.RelDataPermissionsService;
import com.fowo.api.sys.rdp.model.SearchRelDataPermissions;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.ASTNodeAccessImpl;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import org.springframework.util.StringUtils;

import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;

/**
 * 提供 PageSearch 类型的扩展方法
 * @author yl_ls
 */
@Slf4j
public final class PageSearchHelper {
    private static final Map<String, PageSearchSqlDialect> SQL_DIALECT_MAP = Map.of(
            "sqlserver", new SqlServerPageSearchSqlDialect(),
            "mysql", new MySqlPageSearchSqlDialect()
    );

    /** 默认使用模糊搜索的数据类型 */
    private static final List<String> DEFAULT_LIKE_SEARCH_TYPES = List.of("TEXT", "LONGTEXT", "SERIAL");

    private PageSearchHelper(){}

    /**
     * 按数据源类型获取 SQL 方言处理器
     * @param dbType 数据源类型
     * @return SQL 方言处理器
     */
    public static PageSearchSqlDialect sqlDialectByDbType(String dbType) {
        return SQL_DIALECT_MAP.get(dbType);
    }

    /**
     * 填充 pageSearch 上的 sqlJoinSegment 和 sqlWhereSegment
     *
     * @param pageSearch 分页搜索参数
     * @param metaDataObject 源数据对象
     */
    @SneakyThrows
    public static void fillSqlSegments(PageSearch pageSearch, MetaDataObject metaDataObject) {
        if (metaDataObject == null) {
            throw new RException("处理搜索必需要元数据对象");
        }
        List<Join> joins = new ArrayList<>();
        Expression empty = CCJSqlParserUtil.parseExpression("1 = 1");
        Expression where = empty;
        // 高级搜索
        where = superQueryFilter(pageSearch, metaDataObject, where, joins);
        // 一般搜索
        where = baseFilter(pageSearch, metaDataObject, where, joins);
        // 关键字搜索
        where = keywordFilter(pageSearch, metaDataObject, where, joins);
        // 关联数据权限
        where = applyRelData(pageSearch, metaDataObject, where);

        // 回写结果
        if (empty == where) {
            pageSearch.setCustomSqlWhereSegments("");
        } else {
            pageSearch.setCustomSqlWhereSegments(where.toString().substring(5));
        }
        if (joins.isEmpty()) {
            pageSearch.setCustomSqlJoinSegments("");
        } else {
            pageSearch.setCustomSqlJoinSegments(joins.stream().map(Join::toString).collect(Collectors.joining(" ")));
        }
    }

    private static Expression applyRelData(PageSearch pageSearch, MetaDataObject metaDataObject, Expression where) {
        if (pageSearch.getCurrentUserId() != null) {
            String pageSearchClassName = pageSearch.getClass().getName();
            RelDataPermissionsService service = ContextHolder.getApplicationContext().getBean(RelDataPermissionsService.class);
            SearchRelDataPermissions searchParam = service.getFormSearchParam(pageSearchClassName);
            if (searchParam != null) {
                String andExp = service.createWherePart(searchParam, false, pageSearch.getCurrentUserId());
                if (StringUtils.hasText(andExp)) {
                    try {
                        Expression exp = CCJSqlParserUtil.parseExpression(andExp);
                        return new AndExpression().withLeftExpression(where).withRightExpression(exp);
                    } catch (JSQLParserException e) {
                        throw new RuntimeException(e);
                    }
                }
            }
        }
        return where;
    }

    private static Expression superQueryFilter(PageSearch pageSearch, MetaDataObject metaDataObject, Expression where, List<Join> joins) {
        if(pageSearch.getFinalFilters() != null && !pageSearch.getFinalFilters().isEmpty()) {
            List<Expression> andParts = new ArrayList<>();
            Table mainTable = new Table().withName(metaDataObject.getTableName()).withAlias(new Alias("t"));
            boolean isJoinDisplay;
            Map<String, Join> optionalJoin = new HashMap<>();
            for (int i = 0; i < pageSearch.getFinalFilters().size(); i++) {
                PageSearchFilter pageSearchFilter = pageSearch.getFinalFilters().get(i);
                //子表字段结构判断 20240426
                List<String> dataIndex = StrUtil.split(pageSearchFilter.getDataIndex().toString(), '@');
                if (dataIndex.size() > 1) {
                    MetaDataField metaDataChildRootField = ListUtils.find(metaDataObject.getFields(), f -> f.getName().equals(dataIndex.get(0)));
                    if (metaDataChildRootField == null) {
                        continue;
                    }
                    //找到对应的子表字段
                    MetaDataObject childMetaData0bject = MetaDataObjects.getObject(metaDataChildRootField.getRefObjectName());
                    if (childMetaData0bject == null) {
                        continue;
                    }
                    //找到要搜索的子表对象上的字段
                    MetaDataField metaDataChildField = ListUtils.find(childMetaData0bject.getFields(), f -> f.getName().equals(dataIndex.get(1)));
                    if (metaDataChildField == null) {
                        continue;
                    }
                    //dataType -> PARENT  && refObjectName -> 对象名称
                    MetaDataField metaDataChildPrimaryField = ListUtils.find(childMetaData0bject.getFields(), f -> f.getDataType().equals("PARENT") && f.getRefObjectName().equals(metaDataObject.getName()));
                    optionalJoin.computeIfAbsent(childMetaData0bject.getName(), n -> {
                        ///应用子表搜索
                        String childAlias = String.format("Rc_%d", optionalJoin.size());
                        Join join = new Join().withLeft(true).withRightItem(new Table(childMetaData0bject.getTableName()).withAlias(new Alias(childAlias)));
                        //默认关联字段
                        List<Expression> andJoinParts =new ArrayList<>();
                        andJoinParts.add(new EqualsTo(columnExp(childAlias, "del_flag"),new LongValue(0)));
                        if (metaDataChildRootField.getReferenceCustomFields() == null) {
                            andJoinParts.add(new EqualsTo(columnExp("t", "id"), columnExp(childAlias, metaDataChildPrimaryField.getColumnName())));
                        }
                        //自定关联字段
                        else {
                            for (ReferenceCustomField referenceCustomField : metaDataChildRootField.getReferenceCustomFields()) {
                                MetaDataField metaDataHostField = ListUtils.find(metaDataObject.getFields(), f -> f.getName().equals(referenceCustomField.getHostField()));
                                MetaDataField metaDataClientField = ListUtils.find(childMetaData0bject.getFields(), f -> f.getName().equals(referenceCustomField.getClientField()));
                                andJoinParts.add(new EqualsTo(columnExp("t", metaDataHostField.getColumnName()), columnExp(childAlias, metaDataClientField.getColumnName())));
                            }
                        }
                        pageSearch.setNeedGroupBy(true);
                        join.addOnExpression(andExpressionList(andJoinParts));
                        joins.add(join);
                        return join;
                    });
                    superQueryFieldChildFilter(andParts, pageSearchFilter, metaDataChildField, String.format("search.finalFilters[%d]", i), optionalJoin.get(childMetaData0bject.getName()));
                } else {
                    MetaDataField metaDataField = ListUtils.find(metaDataObject.getFields(), f -> f.getName().equals(pageSearchFilter.getDataIndex().toFieldName()));
                    isJoinDisplay = false;
                    if (metaDataField == null) {
                        metaDataField = ListUtils.find(metaDataObject.getFields(), f -> Objects.equals(f.getDisplayFieldName(), pageSearchFilter.getDataIndex().toFieldName()));
                        isJoinDisplay = true;
                    }
                    if (metaDataField == null) {
                        log.warn("SuperQueryField 源字段 {} 未知，过滤已忽略", pageSearchFilter.getDataIndex());
                        continue;
                    }
                    if (isJoinDisplay) {
                        superQueryJoinFieldFilter(andParts, pageSearch, pageSearchFilter, metaDataField, String.format("search.finalFilters[%d]", i), mainTable);
                    } else {
                        superQueryFieldFilter(andParts, pageSearchFilter, metaDataField, String.format("search.finalFilters[%d]", i), mainTable);
                    }
                }
            }
            Expression expression = "or".equals(pageSearch.getSuperQueryMatchType()) ? orExpressionList(andParts) : andExpressionList(andParts);
            if (expression != null) {
                return new AndExpression(where, new Parenthesis(expression));
            }
        }
        return where;
    }


    private static Expression columnExp(String tableAlias, String column) {
        return new Column().withColumnName(column).withTable(new Table().withAlias(new Alias(tableAlias)));
    }


    private static void superQueryFieldChildFilter(List<Expression> andParts, PageSearchFilter pageSearchFilter, MetaDataField metaDataField, String filterAccessName, Join childJoin) {
        Table mainTable = (Table) childJoin.getRightItem();
        if ("eq".equals(pageSearchFilter.getOp())) {
            andParts.add(new EqualsTo().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("ne".equals(pageSearchFilter.getOp()) || "neq".equals(pageSearchFilter.getOp())) {
            andParts.add(new NotEqualsTo().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("gt".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("ge".equals(pageSearchFilter.getOp()) || "gte".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("lt".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("le".equals(pageSearchFilter.getOp()) || "lte".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        } else if ("contains".equals(pageSearchFilter.getOp())) {
            andParts.add(likeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        } else if ("notcontains".equals(pageSearchFilter.getOp())) {
            andParts.add(notLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        } else if ("startswith".equals(pageSearchFilter.getOp())) {
            andParts.add(leftLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        } else if ("endswith".equals(pageSearchFilter.getOp())) {
            andParts.add(rightLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        } else if ("isnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)));
        } else if ("isnotnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withNot(true).withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)));
        } else if ("range".equals(pageSearchFilter.getOp()) && pageSearchFilter.getValues().get(0) != null && pageSearchFilter.getValues().get(1) != null) {
            andParts.add(new GreaterThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".values[0]")));
            andParts.add(new MinorThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".values[1]")));
        } else if ("today".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, DateUtils.toString(now, DateUtils.PATTERN_DATE + " 00:00:00.000"), DateUtils.toString(now, DateUtils.PATTERN_DATE + " 23:59:59.999"));
        } else if ("todayBefore".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            andParts.add(new MinorThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new StringValue(DateUtils.toString(now, DateUtils.PATTERN_DATE + " 00:00:00.000"))));
        } else if ("todayAfter".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            andParts.add(new GreaterThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new StringValue(DateUtils.toString(now, DateUtils.PATTERN_DATE + " 23:59:59.999"))));
        } else if ("week".equals(pageSearchFilter.getOp())) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.WEEK_OF_MONTH, 0);
            cal.set(Calendar.DAY_OF_WEEK, 2);
            String start = DateUtils.toDateString(cal.getTime()) + " 00:00:00.00";
            cal.add(Calendar.DATE, 6);
            String end = DateUtils.toDateString(cal.getTime()) + " 23:59:59.999";
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, start, end);
        } else if ("month".equals(pageSearchFilter.getOp())) {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.DATE, 1);
            String start = DateUtils.toDateString(cal.getTime()) + " 00:00:00.00";
            cal.add(Calendar.MONTH, 1);
            cal.set(Calendar.DATE, 0);
            String end = DateUtils.toDateString(cal.getTime()) + " 23:59:59.999";
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, start, end);
        } else if ("range".equals(pageSearchFilter.getOp())) {
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, true, "values[0]", "values[1]");
        } else if ("in".equals(pageSearchFilter.getOp())) {
            ExpressionList inList = new ExpressionList();
            for (int n = 0; n < pageSearchFilter.getValues().size(); n++) {
                inList.addExpressions(new MybatisParameter(String.format("%s.values[%d]", filterAccessName, n)));
            }
            andParts.add(new InExpression().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightItemsList(inList));
        } else {
            log.warn("SuperQueryField 比较类型 {} 未知，过滤已忽略", pageSearchFilter.getOp());
        }
    }


    private static void superQueryJoinFieldFilter(List<Expression> andParts, PageSearch pageSearch, PageSearchFilter pageSearchFilter, MetaDataField metaDataField, String filterAccessName, Table mainTable) {
        MetaDataObject relObject = ListUtils.find(MetaDataObjects.getObjects(), o ->  metaDataField.getRefTableName().equals(o.getTableName()));
        if (relObject == null) {
            return;
        }
        MetaDataField titleField = ListUtils.find(relObject.getFields(), f -> f.getName().equals(relObject.getTitleFieldName()));
        if (titleField == null) {
            return;
        }
        String aliasTableName = pageSearch.getJoinFieldTableAliasMap().get(metaDataField.getName());
        Column relTitleColumn = new Column().withColumnName(titleField.getColumnName()).withTable(new Table().withName(relObject.getTableName()).withAlias(new Alias(aliasTableName)));
        if ("eq".equals(pageSearchFilter.getOp())) {
            andParts.add(new EqualsTo().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("ne".equals(pageSearchFilter.getOp())) {
            andParts.add(new NotEqualsTo().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("gt".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThan().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("ge".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThanEquals().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("lt".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThan().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("le".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThanEquals().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("contains".equals(pageSearchFilter.getOp())) {
            andParts.add(likeExp(relTitleColumn, new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("notcontains".equals(pageSearchFilter.getOp())) {
            andParts.add(notLikeExp(relTitleColumn, new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("startswith".equals(pageSearchFilter.getOp())) {
            andParts.add(leftLikeExp(relTitleColumn, new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("endswith".equals(pageSearchFilter.getOp())) {
            andParts.add(rightLikeExp(relTitleColumn, new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("isnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withLeftExpression(relTitleColumn));
        }
        else if ("isnotnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withNot(true).withLeftExpression(relTitleColumn));
        }
        else if ("range".equals(pageSearchFilter.getOp()) && pageSearchFilter.getValues().get(0) != null && pageSearchFilter.getValues().get(1) != null) {
            andParts.add(new GreaterThanEquals().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".values[0]")));
            andParts.add(new MinorThanEquals().withLeftExpression(relTitleColumn).withRightExpression(new MybatisParameter(filterAccessName + ".values[1]")));
        }
        else if ("in".equals(pageSearchFilter.getOp())) {
            ExpressionList inList = new ExpressionList();
            for (int n = 0; n < pageSearchFilter.getValues().size(); n++) {
                inList.addExpressions(new MybatisParameter(String.format("%s.values[%d]", filterAccessName, n)));
            }
            andParts.add(new InExpression().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightItemsList(inList));
        }
        else {
            log.warn("SuperQueryField 比较类型 {} 未知，过滤已忽略", pageSearchFilter.getOp());
        }
    }

    private static void superQueryFieldFilter(List<Expression> andParts, PageSearchFilter pageSearchFilter, MetaDataField metaDataField, String filterAccessName, Table mainTable) {
        if ("eq".equals(pageSearchFilter.getOp())) {
            andParts.add(new EqualsTo().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("ne".equals(pageSearchFilter.getOp()) || "neq".equals(pageSearchFilter.getOp())) {
            andParts.add(new NotEqualsTo().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("gt".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("ge".equals(pageSearchFilter.getOp()) || "gte".equals(pageSearchFilter.getOp())) {
            andParts.add(new GreaterThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("lt".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("le".equals(pageSearchFilter.getOp()) || "lte".equals(pageSearchFilter.getOp())) {
            andParts.add(new MinorThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("contains".equals(pageSearchFilter.getOp())) {
            andParts.add(likeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("notcontains".equals(pageSearchFilter.getOp())) {
            andParts.add(notLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("startswith".equals(pageSearchFilter.getOp())) {
            andParts.add(leftLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("endswith".equals(pageSearchFilter.getOp())) {
            andParts.add(rightLikeExp(new Column(metaDataField.getColumnName()).withTable(mainTable), new MybatisParameter(filterAccessName + ".value")));
        }
        else if ("isnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)));
        }
        else if ("isnotnull".equals(pageSearchFilter.getOp())) {
            andParts.add(new IsNullExpression().withNot(true).withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)));
        }
        else if ("range".equals(pageSearchFilter.getOp()) && pageSearchFilter.getValues().get(0) != null && pageSearchFilter.getValues().get(1) != null) {
            andParts.add(new GreaterThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".values[0]")));
            andParts.add(new MinorThanEquals().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter(filterAccessName + ".values[1]")));
        }
        else if ("today".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, DateUtils.toString(now, DateUtils.PATTERN_DATE + " 00:00:00.000"), DateUtils.toString(now, DateUtils.PATTERN_DATE + " 23:59:59.999"));
        }
        else if ("todayBefore".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            andParts.add(new MinorThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new StringValue(DateUtils.toString(now, DateUtils.PATTERN_DATE + " 00:00:00.000"))));
        }
        else if ("todayAfter".equals(pageSearchFilter.getOp())) {
            Date now = new Date();
            andParts.add(new GreaterThan().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightExpression(new StringValue(DateUtils.toString(now, DateUtils.PATTERN_DATE + " 23:59:59.999"))));
        }
        else if ("week".equals(pageSearchFilter.getOp())) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.WEEK_OF_MONTH, 0);
            cal.set(Calendar.DAY_OF_WEEK, 2);
            String start = DateUtils.toDateString(cal.getTime()) + " 00:00:00.00";
            cal.add(Calendar.DATE, 6);
            String end = DateUtils.toDateString(cal.getTime()) + " 23:59:59.999";
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, start, end);
        }
        else if ("month".equals(pageSearchFilter.getOp())) {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.DATE, 1);
            String start = DateUtils.toDateString(cal.getTime()) + " 00:00:00.00";
            cal.add(Calendar.MONTH, 1);
            cal.set(Calendar.DATE, 0);
            String end = DateUtils.toDateString(cal.getTime()) + " 23:59:59.999";
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, false, start, end);
        }
        else if ("range".equals(pageSearchFilter.getOp())) {
            superQueryDateFieldFilter(andParts, metaDataField, filterAccessName, mainTable, true, "values[0]", "values[1]");
        }
        else if ("in".equals(pageSearchFilter.getOp())) {
            ExpressionList inList = new ExpressionList();
            for (int n = 0; n < pageSearchFilter.getValues().size(); n++) {
                inList.addExpressions(new MybatisParameter(String.format("%s.values[%d]", filterAccessName, n)));
            }
            andParts.add(new InExpression().withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable)).withRightItemsList(inList));
        }
        else {
            log.warn("SuperQueryField 比较类型 {} 未知，过滤已忽略", pageSearchFilter.getOp());
        }
    }

    private static void superQueryDateFieldFilter(List<Expression> andParts, MetaDataField metaDataField, String filterAccessName, Table mainTable, boolean getName, String start, String end) {
        Expression startExp = StringUtils.hasText(start) ? (getName ? new MybatisParameter(filterAccessName + "." + start) : new StringValue(start)) : null;
        Expression endExp = StringUtils.hasText(end) ? (getName ? new MybatisParameter(filterAccessName + "." + end) : new StringValue(end)) : null;
        if (startExp != null) {
            andParts.add(new GreaterThanEquals()
                    .withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable))
                    .withRightExpression(startExp)
            );
        }
        if (endExp != null) {
            andParts.add(new MinorThanEquals()
                    .withLeftExpression(new Column(metaDataField.getColumnName()).withTable(mainTable))
                    .withRightExpression(endExp)
            );
        }
    }

    private static Expression notLikeExp(Expression left, Expression parameter) {
        return new LikeExpression().withNot(true).withLeftExpression(left).withRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                new StringValue("%"),
                parameter,
                new StringValue("%")
        )));
    }

    private static Expression likeExp(Expression left, Expression parameter) {
        return new LikeExpression().withLeftExpression(left).withRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                new StringValue("%"),
                parameter,
                new StringValue("%")
        )));
    }

    private static Expression leftLikeExp(Expression left, Expression parameter) {
        return new LikeExpression().withLeftExpression(left).withRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                parameter,
                new StringValue("%")
        )));
    }

    private static Expression rightLikeExp(Expression left, Expression parameter) {
        return new LikeExpression().withLeftExpression(left).withRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                new StringValue("%"),
                parameter
        )));
    }

    private static Expression baseFilter(PageSearch pageSearch, MetaDataObject metaDataObject, Expression where, List<Join> joins) {
        List<Expression> andParts = new ArrayList<>();
        Table mainTable = new Table().withName(metaDataObject.getTableName()).withAlias(new Alias("t"));
        for (MetaDataField field : metaDataObject.getFields()) {
            if (DEFAULT_LIKE_SEARCH_TYPES.contains(field.getDataType()) || ("ENUM".equals(field.getDataType()) && !Boolean.TRUE.equals(field.getMultiple()))) {
                hasPresenceString(pageSearch, field.getName(), str -> {
                    if (Boolean.TRUE.equals(field.getFuzzySearchLeftLike())) {
                        andParts.add(leftLikeExp(new Column(field.getColumnName()).withTable(mainTable), new MybatisParameter("search." + field.getName())));
                    } else {
                        andParts.add(likeExp(new Column(field.getColumnName()).withTable(mainTable), new MybatisParameter("search." + field.getName())));
                    }
                });
                baseStringFieldFilter(andParts, pageSearch, metaDataObject.getDbType(), field, mainTable);
            }
            else if ("ENUM".equals(field.getDataType())) {
                // 枚举多选
                baseMultiEnumFieldFilter(andParts, pageSearch, metaDataObject.getDbType(), field, mainTable);
            }
            //任凯 20240301
            else if ("PARENT".equals(field.getDataType()) || ("JOIN".equals(field.getDataType()) && !Boolean.TRUE.equals(field.getMultiple())) || "LONG".equals(field.getDataType())) {
                baseLongFieldFilter(andParts, pageSearch, field, mainTable, metaDataObject.getDbType());
            }
            else if ("JOIN".equals(field.getDataType()) && Boolean.TRUE.equals(field.getMultiple())) {
                // 关联多选
                baseMultiJoinFieldFilter(andParts, pageSearch, metaDataObject.getDbType(), field, mainTable);
            }
            else if ("DATE".equals(field.getDataType()) || "DATETIME".equals(field.getDataType())) {
                baseDateFieldFilter(andParts, pageSearch, metaDataObject.getDbType(), field, mainTable);
            }
            else if ("BOOLEAN".equals(field.getDataType())) {
                hasPresenceBoolean(pageSearch, field.getName(), bl -> {
                    // 布尔比较直接转换成静态值
                    LongValue lv = new LongValue(bl ? 1 : 0);
                    andParts.add(new EqualsTo().withLeftExpression(new Column(field.getColumnName()).withTable(mainTable)).withRightExpression(lv));
                });
                hasPresenceBoolean(pageSearch, field.getName() + "Eq", bl -> {
                    // 布尔比较直接转换成静态值
                    LongValue lv = new LongValue(bl ? 1 : 0);
                    andParts.add(new EqualsTo().withLeftExpression(new Column(field.getColumnName()).withTable(mainTable)).withRightExpression(lv));
                });
            }
            else if ("INT".equals(field.getDataType()) || "FLOAT".equals(field.getDataType()) || "DOUBLE".equals(field.getDataType()) || "DECIMAL".equals(field.getDataType())) {
                baseNumberFieldFilter(andParts, pageSearch, field, mainTable);
            }
        }
        baseAllOptionJoinFilter(andParts, pageSearch, metaDataObject, joins);
        Expression expression = andExpressionList(andParts);
        if (expression != null) {
            return new AndExpression(where, new Parenthesis(expression));
        }
        return where;
    }

    private static void baseAllOptionJoinFilter(List<Expression> andParts, PageSearch pageSearch, MetaDataObject metaDataObject, List<Join> joins) {
        Map<String, OptionalJoinField> optionalJoinFields = pageSearch.getOptionalJoinFields();
        for (Map.Entry<String, OptionalJoinField> optionalJoinFieldEntry : optionalJoinFields.entrySet()) {
            MetaDataField finalJoinField = getFinalJoinField(metaDataObject, optionalJoinFieldEntry.getKey());
            if (finalJoinField == null) {
                continue;
            }

            // 默认过滤器
            String baseSearchFieldName = optionalJoinFieldEntry.getKey().replaceAll("\\.", "_");
            MybatisParameter val = new MybatisParameter("search." + baseSearchFieldName);
            hasPresence(pageSearch, baseSearchFieldName, obj -> {
                String tableAlias = addJoinIfNeed(joins, optionalJoinFields, optionalJoinFieldEntry.getValue());
                updateNeedGroupBy(pageSearch, optionalJoinFieldEntry.getValue());
                Column column = new Column(optionalJoinFieldEntry.getValue().getColumnName()).withTable(new Table().withAlias(new Alias(tableAlias)));
                if (DEFAULT_LIKE_SEARCH_TYPES.contains(finalJoinField.getDataType())) {
                    // 文本类型的搜索默认为模糊搜索
                    if (Boolean.TRUE.equals(finalJoinField.getFuzzySearchLeftLike())) {
                        andParts.add(leftLikeExp(column, val));
                    } else {
                        andParts.add(likeExp(column, val));
                    }
                } else {
                    andParts.add(new EqualsTo(column, val));
                }
            });

            // InList 过滤器
            String inListFieldName = String.format("%sInList", baseSearchFieldName);
            hasPresenceStringList(pageSearch, inListFieldName, strSet -> {
                String tableAlias = addJoinIfNeed(joins, optionalJoinFields, optionalJoinFieldEntry.getValue());
                updateNeedGroupBy(pageSearch, optionalJoinFieldEntry.getValue());
                Column column = new Column(optionalJoinFieldEntry.getValue().getColumnName()).withTable(new Table().withAlias(new Alias(tableAlias)));
                inListFor(andParts, column, inListFieldName, strSet);
            });

            // 任凯 20240318 1:Begin -> Start
            // 日期类型有 Start,End
            if ("DATE".equals(finalJoinField.getDataType()) || "DATETIME".equals(finalJoinField.getDataType())) {
                String beginFieldName = String.format("%sStart", baseSearchFieldName);
                hasPresenceString(pageSearch, beginFieldName, str -> {
                    String tableAlias = addJoinIfNeed(joins, optionalJoinFields, optionalJoinFieldEntry.getValue());
                    updateNeedGroupBy(pageSearch, optionalJoinFieldEntry.getValue());
                    Column column = new Column(optionalJoinFieldEntry.getValue().getColumnName()).withTable(new Table().withAlias(new Alias(tableAlias)));
                    andParts.add(new GreaterThanEquals().withLeftExpression(column).withRightExpression(new MybatisParameter("search." + beginFieldName)));
                });
                String endFieldName = String.format("%sEnd", baseSearchFieldName);
                hasPresenceString(pageSearch, endFieldName, str -> {
                    String tableAlias = addJoinIfNeed(joins, optionalJoinFields, optionalJoinFieldEntry.getValue());
                    updateNeedGroupBy(pageSearch, optionalJoinFieldEntry.getValue());
                    Column column = new Column(optionalJoinFieldEntry.getValue().getColumnName()).withTable(new Table().withAlias(new Alias(tableAlias)));
                    andParts.add(new MinorThanEquals().withLeftExpression(column).withRightExpression(new MybatisParameter("search." + endFieldName)));
                });
            }
        }
    }

    private static void updateNeedGroupBy(PageSearch pageSearch, OptionalJoinField value) {
        if (!pageSearch.isNeedGroupBy() && Boolean.TRUE.equals(value.getNeedGroupBy())) {
            pageSearch.setNeedGroupBy(true);
        }
    }

    private static String addJoinIfNeed(List<Join> joins, Map<String, OptionalJoinField> optionalJoinFields, OptionalJoinField joinField) {
        if (joins.stream().noneMatch(j -> Objects.equals(getAlias(j.getRightItem()), joinField.getTableAlias()))) {
            if (joinField.getDepends() != null && !joinField.getDepends().isEmpty()) {
                // 依赖注入
                for (String depend : joinField.getDepends()) {
                    OptionalJoinField dependJoin = optionalJoinFields.get(depend);
                    if (dependJoin != null) {
                        addJoinIfNeed(joins, optionalJoinFields, dependJoin);
                    }
                }
            }
            try {
                Statement parse = CCJSqlParserUtil.parse("select t.* from fake_table as t " + joinField.getJoinPart());
                PlainSelect plainSelect = ((Select)parse).getSelectBody(PlainSelect.class);
                joins.addAll(plainSelect.getJoins());
            } catch (JSQLParserException e) {
                throw new RuntimeException(e);
            }
        }
        return joinField.getTableAlias();
    }

    private static String getAlias(FromItem fromItem) {
        if (fromItem.getAlias() == null) {
            return null;
        }
        return fromItem.getAlias().getName();
    }

    private static MetaDataField getFinalJoinField(MetaDataObject metaDataObject, String path) {
        String[] paths = path.split("\\.");
        MetaDataObject parentObject = metaDataObject;
        for(int i = 0; i < paths.length; i++) {
            String partPath = paths[i];
            MetaDataField metaDataField = ListUtils.find(parentObject.getFields(), f -> f.getName().equals(partPath));
            if (metaDataField == null) {
                break;
            }
            if (paths.length - 1 == i) {
                return metaDataField;
            }
            parentObject = ListUtils.find(MetaDataObjects.getObjects(), o -> o.getTableName().equals(metaDataField.getRefTableName()));
            if (parentObject == null) {
                break;
            }
        }
        return null;
    }

    private static void baseNumberFieldFilter(List<Expression> andParts, PageSearch pageSearch, MetaDataField field, Table mainTable) {

        hasPresence(pageSearch, field.getName(), str -> {
            andParts.add(new EqualsTo().withLeftExpression(new Column(field.getColumnName()).withTable(mainTable)).withRightExpression(new MybatisParameter("search." + field.getName())));
        });

        String eqFieldName = String.format("%sEq", field.getName());
        hasPresence(pageSearch, eqFieldName, l -> {
            andParts.add(new EqualsTo()
                    .withLeftExpression(new Column().withColumnName(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + eqFieldName))
            );
        });

        String minFieldName = String.format("%sMin", field.getName());
        hasPresence(pageSearch, minFieldName, l -> {
            andParts.add(new GreaterThanEquals()
                    .withLeftExpression(new Column().withColumnName(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + minFieldName))
            );
        });

        String maxFieldName = String.format("%sMax", field.getName());
        hasPresence(pageSearch, maxFieldName, l -> {
            andParts.add(new MinorThanEquals()
                    .withLeftExpression(new Column().withColumnName(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + maxFieldName))
            );
        });
    }

    private static void baseDateFieldFilter(List<Expression> andParts, PageSearch pageSearch, String dbType, MetaDataField field, Table mainTable) {
        boolean hasTime = "DATETIME".equals(field.getDataType());
        String startFieldName = String.format("%sStart", field.getName());
        hasPresenceString(pageSearch, startFieldName, str -> {
            andParts.add(new GreaterThanEquals()
                    .withLeftExpression(new Column(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + startFieldName))
            );
        });
        hasPresenceDate(pageSearch, startFieldName, date -> {
            andParts.add(new GreaterThanEquals()
                    .withLeftExpression(new Column(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + startFieldName))
            );
        });

        String endFieldName = String.format("%sEnd", field.getName());
        hasPresenceString(pageSearch, endFieldName, str -> {
            andParts.add(new MinorThanEquals()
                    .withLeftExpression(new Column(field.getColumnName()).withTable(mainTable))
                    .withRightExpression(new MybatisParameter("search." + endFieldName))
            );
        });
        hasPresenceDate(pageSearch, endFieldName, date -> {
            if (hasTime) {
                // 日期时间的范围结束值需要加一天小于处理
                andParts.add(new MinorThan()
                        .withLeftExpression(new Column(field.getColumnName()).withTable(mainTable))
                        .withRightExpression(new StringValue(DateUtils.toDateString(DateUtils.addDays(date, 1))))
                );
            } else {
                andParts.add(new MinorThanEquals()
                        .withLeftExpression(new Column(field.getColumnName()).withTable(mainTable))
                        .withRightExpression(new MybatisParameter("search." + endFieldName))
                );
            }
        });
    }

    private static void baseMultiEnumFieldFilter(List<Expression> andParts, PageSearch pageSearch, String dbType, MetaDataField field, Table mainTable) {

        String inListFieldName = String.format("%sInList", field.getName());
        hasPresenceStringList(pageSearch, inListFieldName, strSets -> {
            if (Boolean.TRUE.equals(field.getMultiple())) {
                // 多选字段的 InList
                inListForSets(andParts, dbType, new Column(field.getColumnName()).withTable(mainTable), inListFieldName, strSets);
            } else {
                inListFor(andParts, new Column(field.getColumnName()).withTable(mainTable), inListFieldName, strSets);
            }
        });

        hasPresenceString(pageSearch, field.getName(), str -> {
            PageSearchSqlDialect pageSearchSqlDialect = SQL_DIALECT_MAP.get(dbType);
            if (pageSearchSqlDialect == null) {
                log.error("未能找到指定数据库类型 {} 的搜索方言支持程序，过滤处理已跳过！", dbType);
                return;
            }
            Column column = new Column(field.getColumnName()).withTable(mainTable);
            Expression val = new MybatisParameter("search." + field.getName());
            Expression exp = pageSearchSqlDialect.stringSetFieldInExpression(column, val);
            if (exp != null) {
                andParts.add(exp);
            }
        });
    }

//    private static void baseMultiJoinFieldFilter(List<Expression> andParts, PageSearch pageSearch, String dbType, MetaDataField field, Table mainTable) {
//        hasPresenceLong(pageSearch, field.getName(), str -> {
//            PageSearchSqlDialect pageSearchSqlDialect = SQL_DIALECT_MAP.get(dbType);
//            if (pageSearchSqlDialect == null) {
//                log.error("未能找到指定数据库类型 {} 的搜索方言支持程序，过滤处理已跳过！", dbType);
//                return;
//            }
//            Column column = new Column(field.getColumnName()).withTable(mainTable);
//            Expression val = new MybatisParameter("search." + field.getName());
//            Expression exp = pageSearchSqlDialect.jsonArrayFieldInExpression(column, val);
//            if (exp != null) {
//                andParts.add(exp);
//            }
//        });
//    }

    //任凯 20240301
    private static void baseMultiJoinFieldFilter(List<Expression> andParts, PageSearch pageSearch, String dbType, MetaDataField field, Table mainTable) {
        String inListFieldName = String.format("%sInList", field.getName());
        hasPresenceLongList(pageSearch, inListFieldName , strSets -> {
            PageSearchSqlDialect pageSearchSqlDialect = SQL_DIALECT_MAP.get(dbType);
            List<Expression> orExpressions = new ArrayList<>();
            for (int i = 0; i < strSets.size(); i++) {
                MybatisParameter mybatisParameter = new MybatisParameter("search." + inListFieldName + "[" + i + "]");
                orExpressions.add(pageSearchSqlDialect.jsonArrayFieldInExpression(new Column(field.getColumnName()).withTable(mainTable), mybatisParameter));
            }

            if (orExpressions.isEmpty()) {
                return;
            }
            if (orExpressions.size() == 1) {
                andParts.add(orExpressions.get(0));
                return;
            }

            Parenthesis parenthesis = new Parenthesis();
            OrExpression orExpression = new OrExpression().withLeftExpression(orExpressions.get(0));
            parenthesis.setExpression(orExpression);
            for (int i = 1; i < orExpressions.size() - 1; i++) {
                OrExpression nextOrExpression = new OrExpression().withLeftExpression(orExpressions.get(i));
                orExpression.setRightExpression(nextOrExpression);
                orExpression = nextOrExpression;
            }
            orExpression.setRightExpression(orExpressions.get(orExpressions.size() - 1));
            andParts.add(parenthesis);
        });
    }


    private static void baseLongFieldFilter(List<Expression> andParts, PageSearch pageSearch, MetaDataField field, Table mainTable, String dbType) {
        baseInListFilter(andParts, pageSearch, field, mainTable, dbType);
        baseNumberFieldFilter(andParts, pageSearch, field, mainTable);
    }

    private static void baseInListFilter(List<Expression> andParts, PageSearch pageSearch, MetaDataField field, Table mainTable, String dbType) {
        String inListFieldName = String.format("%sInList", field.getName());
        hasPresenceStringList(pageSearch, inListFieldName, strSets -> {
            if (Boolean.TRUE.equals(field.getMultiple())) {
                // 多选字段的 InList
                inListForSets(andParts, dbType, new Column(field.getColumnName()).withTable(mainTable), inListFieldName, strSets);
            } else {
                inListFor(andParts, new Column(field.getColumnName()).withTable(mainTable), inListFieldName, strSets);
            }
        });
    }

    private static void inListForSets(List<Expression> andParts, String dbType, Column column, String inListFieldName, List<String> strSets) {
        PageSearchSqlDialect pageSearchSqlDialect = SQL_DIALECT_MAP.get(dbType);
        List<Expression> orExpressions = new ArrayList<>();
        for (int i = 0; i < strSets.size(); i++) {
            MybatisParameter mybatisParameter = new MybatisParameter("search." + inListFieldName + "[" + i + "]");
            orExpressions.add(pageSearchSqlDialect.stringSetFieldInExpression(column, mybatisParameter));
        }

        if (orExpressions.isEmpty()) {
            return;
        }
        if (orExpressions.size() == 1) {
            andParts.add(orExpressions.get(0));
            return;
        }

        Parenthesis parenthesis = new Parenthesis();
        OrExpression orExpression = new OrExpression().withLeftExpression(orExpressions.get(0));
        parenthesis.setExpression(orExpression);
        for (int i = 1; i < orExpressions.size() - 1; i++) {
            OrExpression nextOrExpression = new OrExpression().withLeftExpression(orExpressions.get(i));
            orExpression.setRightExpression(nextOrExpression);
            orExpression = nextOrExpression;
        }
        orExpression.setRightExpression(orExpressions.get(orExpressions.size() - 1));
        andParts.add(parenthesis);
    }

    private static void inListFor(List<Expression> andParts, Column column, String inListFieldName, List<String> strSets) {
        ExpressionList expressionList = new ExpressionList();
        for (int i = 0; i < strSets.size(); i++) {
            expressionList.addExpressions(new MybatisParameter("search." + inListFieldName + "[" + i + "]"));
        }

        andParts.add(new InExpression().withLeftExpression(column).withRightItemsList(expressionList));
    }

    private static void baseStringFieldFilter(List<Expression> andParts, PageSearch pageSearch, String dbType, MetaDataField field, Table mainTable) {
        String eqFieldName = String.format("%sEq", field.getName());
        hasPresenceString(pageSearch, eqFieldName, strSets -> {
            andParts.add(new EqualsTo().withLeftExpression(new Column(field.getColumnName()).withTable(mainTable)).withRightExpression(new Parenthesis(new MybatisParameter("search." + eqFieldName))));
        });
        String neqFieldName = String.format("%sNeq", field.getName());
        hasPresenceString(pageSearch, neqFieldName, strSets -> {
            andParts.add(new NotEqualsTo().withLeftExpression(new Column(field.getColumnName()).withTable(mainTable)).withRightExpression(new Parenthesis(new MybatisParameter("search." + neqFieldName))));
        });
        String containsFieldName = String.format("%sContains", field.getName());
        hasPresenceString(pageSearch, containsFieldName, str -> {
            andParts.add(likeExp(
                    new Column(field.getColumnName()).withTable(mainTable),
                    new MybatisParameter("search." + containsFieldName)
            ));
        });
        baseInListFilter(andParts, pageSearch, field, mainTable, dbType);
    }

    private static Expression keywordFilter(PageSearch pageSearch, MetaDataObject metaDataObject, Expression where, List<Join> joins) {
        String keyword = pageSearch.getKeyword();
        if (!StringUtils.hasText(keyword)) {
            return where;
        }
        List<Expression> andParts = new ArrayList<>();
        Table mainTable = new Table().withName(metaDataObject.getTableName()).withAlias(new Alias("t"));
        for (MetaDataField field : metaDataObject.getFields()) {
            if ("TEXT".equals(field.getDataType()) || "SERIAL".equals(field.getDataType())) {
                LikeExpression likeExpression = new LikeExpression();
                likeExpression.setLeftExpression(new Column().withColumnName(field.getColumnName()).withTable(mainTable));
                if (Boolean.TRUE.equals(field.getFuzzySearchLeftLike())) {
                    likeExpression.setRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                            new MybatisParameter("search.keyword"), new StringValue("%"))));
                } else {
                    likeExpression.setRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                            new StringValue("%"), new MybatisParameter("search.keyword"), new StringValue("%"))));
                }
                andParts.add(likeExpression);
            }
            else if ("JOIN".equals(field.getDataType()) && !Boolean.TRUE.equals(field.getMultiple())) {
                // 单选关联
                String tblAlias = pageSearch.getJoinFieldTableAliasMap().get(field.getName());
                if (tblAlias != null) {
                    MetaDataObject refObj = ListUtils.find(MetaDataObjects.getObjects(), o -> o.getTableName().equals(field.getRefTableName()));
                    if (refObj != null) {
                        MetaDataField refTitleField = ListUtils.find(refObj.getFields(), f -> f.getName().equals(refObj.getTitleFieldName()));
                        if (refTitleField != null) {
                            Column column = new Column(refTitleField.getColumnName()).withTable(new Table(refObj.getTableName()).withAlias(new Alias(tblAlias)));
                            LikeExpression likeExpression = new LikeExpression();
                            likeExpression.setLeftExpression(column);
                            if (Boolean.TRUE.equals(refTitleField.getFuzzySearchLeftLike())) {
                                likeExpression.setRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                                        new MybatisParameter("search.keyword"), new StringValue("%"))));
                            } else {
                                likeExpression.setRightExpression(new Function().withName("CONCAT").withParameters(new ExpressionList().addExpressions(
                                        new StringValue("%"), new MybatisParameter("search.keyword"), new StringValue("%"))));
                            }
                            andParts.add(likeExpression);
                        }
                    }
                }
            }
        }
        Expression expression = orExpressionList(andParts);
        if (expression != null) {
            return new AndExpression(where, new Parenthesis(expression));
        }
        return where;
    }

    private static Expression andExpressionList(List<Expression> expressions) {
        if (expressions == null || expressions.isEmpty()) {
            return null;
        }
        if (expressions.size() == 1) {
            return expressions.get(0);
        }
        AndExpression rootExpression = new AndExpression();
        rootExpression.setLeftExpression(expressions.get(0));
        AndExpression prevExpression = rootExpression;
        for (int i = 1; i < expressions.size(); i++) {
            if (prevExpression.getRightExpression() == null) {
                prevExpression.setRightExpression(expressions.get(i));
            } else {
                Expression andLeftExpression = prevExpression.getRightExpression();
                AndExpression newExpression = new AndExpression()
                        .withLeftExpression(andLeftExpression)
                        .withRightExpression(expressions.get(i));
                prevExpression.setRightExpression(newExpression);
                prevExpression = newExpression;
            }
        }

        return rootExpression;
    }

    private static Expression orExpressionList(List<Expression> expressions) {
        if (expressions == null || expressions.isEmpty()) {
            return null;
        }
        if (expressions.size() == 1) {
            return expressions.get(0);
        }
        OrExpression rootExpression = new OrExpression();
        rootExpression.setLeftExpression(expressions.get(0));
        OrExpression prevExpression = rootExpression;
        for (int i = 1; i < expressions.size(); i++) {
            if (prevExpression.getRightExpression() == null) {
                prevExpression.setRightExpression(expressions.get(i));
            } else {
                Expression andLeftExpression = prevExpression.getRightExpression();
                OrExpression newExpression = new OrExpression()
                        .withLeftExpression(andLeftExpression)
                        .withRightExpression(expressions.get(i));
                prevExpression.setRightExpression(newExpression);
                prevExpression = newExpression;
            }
        }

        return rootExpression;
    }

    private static void hasPresenceString(Object params, String name, Consumer<String> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val instanceof String) {
                    callback.accept((String)val);
                }
            } catch (Exception ignored) {
            }
        }
    }

    private static void hasPresenceDate(Object params, String name, Consumer<Date> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val instanceof Date) {
                    callback.accept((Date)val);
                }
            } catch (Exception ignored) {
            }
        }
    }

    private static void hasPresenceStringList(Object params, String name, Consumer<List<String>> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val instanceof List && !((List<?>) val).isEmpty() && (((List<?>) val).get(0) instanceof String  ||  ((List<?>) val).get(0) instanceof Long) ){
                    callback.accept((List<String>)val);
                }
            } catch (Exception ignored) {
            }
        }
    }
    //任凯 20240301
    private static void hasPresenceLongList(Object params, String name, Consumer<List<Long>> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val instanceof List && !((List<?>) val).isEmpty()&& ((List<?>) val).get(0) instanceof Long) {
                    callback.accept((List<Long>)val);
                }
            } catch (Exception ignored) {
            }
        }
    }

    private static void hasPresence(Object params, String name, Consumer<Object> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val == null) {
                    return;
                }
                if (val instanceof String && StringUtils.hasText((String) val)) {
                    callback.accept(val);
                }
                else if (val instanceof Collection && !((Collection<?>) val).isEmpty()) {
                    callback.accept(val);
                }
                else {
                    callback.accept(val);
                }
            } catch (Exception ignored) {
            }
        }
    }

    private static void hasPresenceBoolean(Object params, String name, Consumer<Boolean> callback) {
        if (params != null) {
            try {
                Object val = BeanUtil.getFieldValue(params, name);;
                if (val instanceof Boolean) {
                    callback.accept((Boolean) val);
                }
                else if (val instanceof String) {
                    if (Objects.equals(val, "true") || Objects.equals(val, "1")) {
                        callback.accept(true);
                    }
                    else if (Objects.equals(val, "false") || Objects.equals(val, "0")) {
                        callback.accept(false);
                    }
                }
            } catch (Exception ignored) {
            }
        }
    }

//    private static void hasPresenceLong(Object params, String name, Consumer<Long> callback) {
//        if (params != null) {
//            try {
//                Object val = BeanUtil.getFieldValue(params, name);;
//                if (val instanceof Long) {
//                    callback.accept((Long)val);
//                }
//            } catch (Exception ignored) {
//            }
//        }
//    }

    public static class MybatisParameter extends ASTNodeAccessImpl implements Expression {

        public MybatisParameter() {

        }

        public MybatisParameter(String name) {
            this.name = name;
        }

        private String name;

        @Override
        public void accept(ExpressionVisitor expressionVisitor) {

        }

        public MybatisParameter withName(String name) {
            this.name = name;
            return this;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Override
        public String toString() {
            return "#{" + name + "}";
        }
    }
}
